/*
 * Created on Nov 27, 2005
 */
package dbsucxentW.pathProcessorW;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JOptionPane;

/**
 * Manage joining of tables and predicates
 * @author Seah Boon Siew, modified by Lee Tzyy Ming, Klarinda
 */ 
public class WhereSQL {

    private String _sql;
    private String _fromSQL;
    private boolean _init;
    private int _tables;
    private int _rv;
    private String _firstTable;
    private String _attrPath;
    private boolean _showPath;
    private boolean _distinct = false;
    private boolean _onlyReturnAttribute = false;
    private int _noOfNewTables = 0;
    private int _initOldTable = 0;
    private int _lastTables = 0;
    private int _lastTempTable = 0;
    private String _andorExpr = "";
    boolean _ishardcode = false;
    private long[] _rValues; // RValue at level 1 would be stored in _rValues[1] and so forth
    private int[] _rvlevel; // R2.level = 5 -> _rvlevel[2] = 5
    private static final int MAXLEVEL = 25; // assume the max level of RValue to be 25
    private Connection _dbConnection;
    public int _descendantIndex = 0; // added by Wang Geng
    public int _ADMonitor = 0; // added by Wang Geng
    public int _ancestorIndex = 0;

    public WhereSQL(boolean single, boolean showPath) {
        System.out.println("WhereSQL");
        _init = true;
        _sql = "\n";
        _fromSQL = "";
        _firstTable = "";
        _tables = 0;
        _rv = 0;
        _showPath = showPath;
        _attrPath = "";
        _rValues = new long[MAXLEVEL];
        _rvlevel = new int[MAXLEVEL];
    }

    public void addPaths() {
        System.out.println("WhereSQL.addPaths()"); // klarinda 20060629
        if (_onlyReturnAttribute) {
            add("P.pathId = AL.pathId", false);
            _fromSQL += replace(", Path P");
        } else {
            _tables++;
            add("P.pathId = VL.pathId", false);
            _fromSQL += replace(", Path P");
        }
        System.out.println("WhereSQL.addPaths(), _fromSQL: " + _fromSQL); // klarinda
    }

    public int getLastTempTableCounter() {
        return _lastTempTable;
    }

    public void addLastTempTable(int level) {
        _lastTempTable++;
        if (_fromSQL.compareTo("") != 0) {
            _fromSQL += ",";
        }

        if (level == 2) {
            _fromSQL += " (SELECT DOCID, MAX(RID) AS MAXI FROM LASTTEMP WHERE FLAG = " + _lastTempTable + " GROUP BY DOCID) AS L" + _lastTempTable;
            add("VK.docId = L" + _lastTempTable + ".docId", false);
        } else {
            _fromSQL += " (SELECT DOCID, PDOS, MAX(RID) AS MAXI FROM LASTTEMP WHERE FLAG = " + _lastTempTable + " GROUP BY DOCID, PDOS) AS L" + _lastTempTable;
            add("VK.docId = L" + _lastTempTable + ".docId", false);
            add("VL.DeweyOrderSum = L" + _lastTempTable + ".PDOS", false);
        }
    }

    // new PathValue table
    public void newTable() {
        System.out.println("WhereSQL.newTable()"); // klarinda 20060629
        _tables++;

        if (_fromSQL.compareTo("") != 0) {
            _fromSQL += ",";
            if (_ishardcode) { // klarinda 20070426 ; change by erwin to >=
                //if (_rv >= 0)
                //add("VK.docId = VL.docId");
                //else
                //add("VK.docId = RL.docId");
            } else {
                if (_rv >= 0) {
                    // klarinda 20061205; change by erwin to >=
                    add("VK.docId = RK.docId", false);
                } else {
                    add("VK.docId = RL.docId", false);
                }
            }
        } else {
            _firstTable = replace("VK");
        }

        _fromSQL += replace(" PathValue VK");
        System.out.println("WhereSQL.newTable(), _fromSQL: " + _fromSQL);
    }

    public void newDTable(int DIndex) {
        _fromSQL = _fromSQL + ", DescendantTemp D" + DIndex;
        System.out.println("DescendantTemp.newTable, _fromSQL: " + _fromSQL);
    }

    public void newPathTable(int PathIndex) {
        _fromSQL = _fromSQL + ", Path P" + PathIndex;
        System.out.println("Path.newTable, _fromSQL: " + _fromSQL);
    }

    public void newRVTable() {
        _rv++;
        _fromSQL = _fromSQL + ",DocumentRValue R" + _rv;
        System.out.println("Rvalue.newTable, _fromSQL: " + _fromSQL);
    }

    // new DescendantTemp (Wang Geng 20061227)
    public void newATable(int AIndex) {
        _fromSQL = _fromSQL + ", AncestorTemp A" + AIndex;
        System.out.println("AncestorTemp.newTable, _fromSQL: " + _fromSQL);
    }

    // new Attribute table (klarinda 20060901)
    public void newAttributeTable() {
        System.out.println("WhereSQL.newAttributeTable()"); // klarinda 20060629
        _fromSQL += ",";

        if (!_ishardcode) {
            add("AL.docId = VL.docId", false);
        }
        _fromSQL += replace(" Attribute AL");
        System.out.println("WhereSQL.newAttributeTable(), _fromSQL: " + _fromSQL); // klarinda 20060629
    }

    public void newJoinTable() {
        System.out.println("WhereSQL.newJoinTable()");
        if (_fromSQL.compareTo("") != 0) {
            // shd always be > 0
            _fromSQL += ",";
            if (_ishardcode) {
                //if (_rv > 0)
                //	add("VLj.docId = VL.docId");
                //else
                //	add("VLj.docId = RL.docId");
            } else {
                if (_rv == 0) {
                    add("VLj.docId = RL.docId", false);
                    /*else
                    add("VLj.docId = RK.docId");*/
                }
            }
        } else {
            _firstTable = replace("VLj");

            // if (_singleDoc) add("VK.docId = 1"); //hardcode docId for
            // experiment purpose
        }

        _fromSQL += replace(" PathValue VLj");
        System.out.println("WhereSQL.newTable(), _fromSQL: " + _fromSQL); // klarinda
        // 20060629
    }

    // new Join Attribute table (klarinda 20070613)
    // copied with some modifications from newAttributeTable()
    public void newJoinAttributeTable() {
        System.out.println("WhereSQL.newJoinAttributeTable()"); // klarinda
        // 20060629
        _fromSQL += ",";
        add("ALj.docId = VL.docId", false); // j is for join
        _fromSQL += replace(" Attribute ALj"); // j is for join
        System.out.println("WhereSQL.newAttributeTable(), _fromSQL: " + _fromSQL); // klarinda 20060629
    }

    // new DocumentRValue table
    public void newRv(int level) {
        System.out.println("WhereSQL.newRv(" + level + ")"); // klarinda
        // 20060629
        level--;

        if (level > 0) {
            _rv++;

            if (_ishardcode) {
                // klarinda 20070426
                _rvlevel[_rv] = level;
            } else {
                // klarinda 20070426
                if (_fromSQL.compareTo("") != 0) {
                    _fromSQL += ",";
                    add("RK.docId = RL.docid", false);
                } else {
                    _firstTable = replace("RK");
                    // klarinda 20060811 do not hard code docid, -> 20060913
                    // hardcode docid for exp purpose
                    // if (_singleDoc) add("RK.docId = 1");
                }

                // add("RK.level = " + level);
                if (_ADMonitor == 1) {
                    add("RK.level = " + level + "+D" + _descendantIndex + ".Level", false); // added by wang geng
                } else if (_ADMonitor == -1) {
                    add("RK.level = " + level + "+A" + _ancestorIndex + ".Level-2", false);
                } else {
                    add("RK.level = " + level, false);
                }

                _fromSQL += replace("DocumentRValue RK");
            } // klarinda 20070426
            System.out.println("WhereSQL.newRv(" + level + "), _fromSQL: " + _fromSQL); // klarinda 20060629
        }
    }

    public void newRv(String level) {
        _rv++;

        if (_fromSQL.compareTo("") != 0) {
            _fromSQL += ",";
            add("RK.docId = RL.docid", false);
        } else {
            _firstTable = replace("RK");
        }

        add("RK.level = " + level, false);
        _fromSQL += replace("DocumentRValue RK");
    }

    public String replace(String sql) {
        System.out.println("WhereSQL.replace(" + sql + ")");
        String replacer = "V" + _tables;
        sql = sql.replaceAll("VK", replacer);

        int old = _tables - 1;
        if (old < 1) {
            replacer = _firstTable;
        } else {
            replacer = "V" + old;
        }
        sql = sql.replaceAll("VL", replacer);

        replacer = "A" + old;
        sql = sql.replaceAll("AL", replacer);

        replacer = "R" + _rv;
        if (_ishardcode) {
            sql = sql.replaceAll("RK.RValue", "" + _rValues[_rvlevel[_rv]]);
        } else {
            sql = sql.replaceAll("RK", replacer);
        }
        old = _rv - 1;
        if (old < 1) {
            replacer = _firstTable;
        } else {
            replacer = "R" + old;
        }
        if (_ishardcode) {
            if (old < 1) {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[0]]);
            } else {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[old]]);
            }
        } else {
            sql = sql.replaceAll("RL", replacer);
        }
        return sql;
    }

    public void add(String sql, boolean isAddPredicate) {
        System.out.println("WhereSQL.add(" + sql + ")"); // klarinda 20060629
        String replacer = "";
        if (isAddPredicate) {
            replacer = "V" + _lastTables;
        } else {
            replacer = "V" + _tables;
        }

        sql = sql.replaceAll("VK", replacer);

        int old = _tables - 1;
        if (old < 1) {
            replacer = _firstTable;
            sql = sql.replaceAll("VL.DeweyOrderSum", "0");
            sql = sql.replaceAll("VL.SiblingSum", "0");
        } else {
            replacer = "V" + old;
        }
        sql = sql.replaceAll("VL", replacer);


        replacer = "A" + old;
        sql = sql.replaceAll("AL", replacer);

        replacer = "R" + _rv;
        if (_ishardcode) {
            sql = sql.replaceAll("RK.RValue", "" + _rValues[_rvlevel[_rv]]);
        } else {
            sql = sql.replaceAll("RK", replacer);
        }
        old = _rv - 1;
        if (old < 1) {
            replacer = _firstTable;
        } else {
            replacer = "R" + old;
        }
        if (_ishardcode) {
            if (old < 1) {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[0]]);
            } else {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[old]]);
            }
        } else {
            sql = sql.replaceAll("RL", replacer);
        }
        if (!_init) {
            _sql += " AND ";
        } else {
            _sql += " WHERE ";
        }

        _sql += sql + "\n";
        _init = false;
    }

    public String get() {
        System.out.println("WhereSQL.get()");
        String fromSQL;
        // _showPath = false; // TODO: _showPath need to be modified
        if (_showPath) {
            int i = _tables - 1;
            if (_onlyReturnAttribute) {
                fromSQL = "WITH V (leafValue, pathID, branchOrder, DeweyOrderSum, PathExp, DocId, LeafOrder  ) AS ( \n";
                if (_distinct) {
                    fromSQL += " SELECT DISTINCT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, P.pathExp, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder \n FROM "; // klarinda
                } else {
                    fromSQL += " SELECT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, P.pathExp, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder  \n FROM "; // klarinda
                }
                fromSQL += _fromSQL + _sql;
                fromSQL += ") \n" + " SELECT A.leafValue, A.pathID, V.branchOrder, V.DeweyOrderSum, V.pathExp, A.DocId, A.LeafOrder , A.AttrOrder \n" + " FROM Attribute A, V \n" + " WHERE A.DocId = V.DocId AND A.LeafOrder = V.LeafOrder \n";
                if (_attrPath.compareTo("") != 0) {
                    fromSQL += " AND A.PathId in " + _attrPath + " \n";
                }

                fromSQL += " ORDER BY V.DocId, V.DeweyOrderSum, AttrOrder \n"; // 20061110
                if (_tables > 1) {
                    fromSQL += " OPTION (FORCE ORDER)";
                }
            } else {
                if (_distinct) {
                    fromSQL = " SELECT DISTINCT V" + i + ".leafValue, V" + i + ".pathID, V" + i + ".branchOrder, V" + i + ".DeweyOrderSum, P.pathExp, V" + i + ".DocId , V" + i + ".LeafOrder \n FROM "; // klarinda
                } else {
                    fromSQL = " SELECT V" + i + ".leafValue, V" + i + ".pathID, V" + i + ".branchOrder, V" + i + ".DeweyOrderSum, P.pathExp, V" + i + ".DocId , V" + i + ".LeafOrder \n FROM "; // klarinda 20060811
                }
                fromSQL += _fromSQL + _sql + "\nOrder By V" + i + ".DocId , V" + i + ".LeafOrder  \n\n"; // klarinda 20060811 - add
            }
        } else if (_onlyReturnAttribute) {
            fromSQL = "WITH V (leafValue, pathID, branchOrder, DeweyOrderSum, DocId, LeafOrder  ) AS ( \n";
            if (_distinct) {
                fromSQL += " SELECT DISTINCT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder \n FROM "; // klarinda
            } else {
                fromSQL += " SELECT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder  \n FROM "; // klarinda
            }
            fromSQL += _fromSQL + _sql;
            fromSQL += ") \n" + " SELECT A.leafValue, A.pathID, V.branchOrder, V.DeweyOrderSum, A.DocId, A.LeafOrder , A.AttrOrder \n" + " FROM Attribute A, V \n" + " WHERE A.DocId = V.DocId AND A.LeafOrder = V.LeafOrder \n";
            if (_attrPath.compareTo("") != 0) {
                fromSQL += " AND A.PathId in " + _attrPath + " \n";
            }
            fromSQL += " ORDER BY V.DocId, V.DeweyOrderSum, AttrOrder \n"; // 20061110
            if (_tables > 1) {
                fromSQL += " OPTION (FORCE ORDER)";
            }
        } else {
            if (_distinct) {
                fromSQL = " SELECT DISTINCT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder \n FROM "; // klarinda
            } else {
                fromSQL = " SELECT V" + _tables + ".leafValue, V" + _tables + ".pathID, V" + _tables + ".branchOrder, V" + _tables + ".DeweyOrderSum, V" + _tables + ".DocId,  V" + _tables + ".LeafOrder  \n FROM "; // klarinda
            }
            fromSQL += _fromSQL + _sql;
            fromSQL = unionAllWithAttribute(fromSQL);
            if (_tables > 1) {
                fromSQL += " OPTION (FORCE ORDER) ";
            }
        }

        System.out.println("WhereSQL.get(), return fromSQL: \n" + fromSQL);
        return fromSQL;
    }

    // union the PathValue with Attribute table if there is attribute to be
    // returned (attr pathid is not empty)
    private String unionAllWithAttribute(String sql) {
        String methodname = "WhereSQL.unionAllWithAttribute";
        System.out.println(methodname + "(sql");

        if (_attrPath.equals("()") || _attrPath.equals("(0)")) {
            sql += " ORDER BY DocId, DeweyOrderSum\n";
        } else {
            sql = " WITH V (leafValue, pathID, branchOrder, DeweyOrderSum, DocId, LeafOrder ) AS (\n" + sql + " ) SELECT V.*, 1 as Attr, 0 as AttrOrder \n" + " FROM V \n" + " UNION ALL \n" + " SELECT A.leafValue, A.pathID, V.branchOrder, V.DeweyOrderSum, A.DocId, A.LeafOrder, 0 as Attr, A.AttrOrder \n" + " FROM Attribute A, V \n" + " WHERE A.DocId = V.DocId AND A.LeafOrder = V.LeafOrder \n";
            if (_attrPath.compareTo("") != 0) {
                sql += " AND A.PathId in " + _attrPath + " \n";
            }
            sql += " ORDER BY DocId, DeweyOrderSum, Attr, AttrOrder\n";
        }

        return sql;
    }

    public int getTables() {
        return _tables;
    }

    public void setDistinct(boolean distinct) {
        _distinct = distinct;
    }

    public void setAttributePath(String attrPath) {
        System.out.println("WhereSQL.setAttributePath(" + attrPath + ")"); // klarinda
        _attrPath = attrPath;
    }

    public void setonlyReturnAttribute(boolean onlyAttr) {
        _onlyReturnAttribute = onlyAttr;
    }

    public void setNoOfNewTables(int newTables) {
        _noOfNewTables = newTables;
        _lastTables = _tables + _noOfNewTables;
        _initOldTable = _tables - 1;
        _andorExpr = "";
    }

    public void addAndOrExpr(boolean and, boolean or, int noOfOpenParenthesis, String sql, int noOfCloseParenthesis) {
        System.out.println();
        int i;
        _andorExpr += "\t";
        if (and) {
            _andorExpr += "AND ";
        } else if (or) {
            _andorExpr += "OR ";
        }
        for (i = 0; i < noOfOpenParenthesis; i++) {
            _andorExpr += "( ";
        }
        String replacer = "V" + _lastTables; // this is the only diff with
        sql = sql.replaceAll("VK", replacer);

        int old = _tables - 1;
        if (old < 1) {
            replacer = _firstTable;
            sql = sql.replaceAll("VL.DeweyOrderSum", "0");
            sql = sql.replaceAll("VL.SiblingSum", "0");
        } else {
            replacer = "V" + old;
        }
        sql = sql.replaceAll("VL", replacer);

        // VM (additional compared to method add()
        if (_initOldTable < 1) {
            replacer = _firstTable;
            sql = sql.replaceAll("VM.DeweyOrderSum", "0");
            sql = sql.replaceAll("VM.SiblingSum", "0");
        } else {
            replacer = "V" + _initOldTable;
        }
        sql = sql.replaceAll("VM", replacer);

        replacer = "A" + old;
        sql = sql.replaceAll("AL", replacer);

        replacer = "R" + _rv;
        if (_ishardcode) {
            // klarinda 20070426
            sql = sql.replaceAll("RK.RValue", "" + _rValues[_rvlevel[_rv]]);
        } else {
            sql = sql.replaceAll("RK", replacer);
        }
        old = _rv - 1;
        if (old < 1) {
            replacer = _firstTable;
        } else {
            replacer = "R" + old;
        }
        if (_ishardcode) {
            // klarinda 20070426
            if (old < 1) {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[0]]);
            } else {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[old]]);
            }
        } else {
            sql = sql.replaceAll("RL", replacer);
        }
        _andorExpr += sql;

        for (i = 0; i < noOfCloseParenthesis; i++) {
            _andorExpr += " ) ";
        }
        _andorExpr += "\n";
    }

    // klarinda 20070108 for MINT query only
    public void addAndOrExprMINT(boolean and, boolean or, int noOfOpenParenthesis, String sql, int noOfCloseParenthesis) {
        System.out.println();
        int i;
        _andorExpr += "\t";
        if (and) {
            _andorExpr += "AND ";
        } else if (or) {
            _andorExpr += "OR ";
        }
        for (i = 0; i < noOfOpenParenthesis; i++) {
            _andorExpr += "( ";
        }
        String replacer = "V" + _tables; // this is the only diff with method
        sql = sql.replaceAll("VK", replacer);

        int old = _tables - 1;
        if (old < 1) {
            replacer = _firstTable;
            sql = sql.replaceAll("VL.DeweyOrderSum", "0");
            sql = sql.replaceAll("VL.SiblingSum", "0");
        } else {
            replacer = "V" + old;
        }
        sql = sql.replaceAll("VL", replacer);

        if (_initOldTable < 1) {
            replacer = _firstTable;
            sql = sql.replaceAll("VM.DeweyOrderSum", "0");
            sql = sql.replaceAll("VM.SiblingSum", "0");
        } else {
            replacer = "V" + _initOldTable;
        }
        sql = sql.replaceAll("VM", replacer);

        replacer = "A" + old;
        sql = sql.replaceAll("AL", replacer);

        replacer = "R" + _rv;
        if (_ishardcode) {
            // klarinda 20070426
            if (_rv > 0) {
                sql = sql.replaceAll("RK.RValue", "" + _rValues[_rvlevel[_rv]]);
            } else {
                sql = sql.replaceAll("RK.RValue", "" + _rValues[1]);
            }
        } else {
            sql = sql.replaceAll("RK", replacer);
        }
        replacer = "R" + (_rv + 1);
        if (_ishardcode) {
            sql = sql.replaceAll("RP.RValue", "" + _rValues[_rvlevel[_rv + 1]]);
        } else {
            sql = sql.replaceAll("RP", replacer);
        }
        old = _rv - 1;
        if (old < 1) {
            replacer = _firstTable;
        } else {
            replacer = "R" + old;
        }
        if (_ishardcode) {
            if (old < 1) {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[0]]);
            } else {
                sql = sql.replaceAll("RL.RValue", "" + _rValues[_rvlevel[old]]);
            }
        } else {
            sql = sql.replaceAll("RL", replacer);
        }
        _andorExpr += sql;

        for (i = 0; i < noOfCloseParenthesis; i++) {
            _andorExpr += " ) ";
        }
        _andorExpr += "\n";
    }

    public void endOfPredicate() {
        System.out.println("WhereSQL.endOfPredicate()");
        if (!_init) {
            _sql += " AND ";
        } else {
            _sql += " WHERE ";
        }

        _sql += " \n (\n" + _andorExpr + " )\n";
        _init = false;
    }

    public boolean ishardcode() {
        return _ishardcode;
    }

    public void sethardcode(boolean ishardcode, Connection connection) {
        _dbConnection = connection;
        _ishardcode = ishardcode;
        if (!ishardcode) {
            return;
        }
        try {
            Statement statement = _dbConnection.createStatement();
            ResultSet results = statement.executeQuery("SELECT * FROM DOCUMENTRVALUE WHERE DOCID = 1 ORDER BY DOCID, LEVEL");
            int i = 0;
            _rValues[i] = 0;
            while (results.next()) {
                i++;
                _rValues[i] = results.getLong("RValue");
                System.out.println("WhereSQL.sethardcode(true) - level/RValue: " + i + " / " + _rValues[i]);
            }
            results.close();
            statement.close();
        } catch (Exception ex) {
            System.out.println("WhereSQL.sethardcode(true) - Error when Query RValue: " + ex.getMessage());
            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error when Query RValue", JOptionPane.ERROR_MESSAGE);
            return;
        }
    }
}